package com.friday.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.friday.dao.LeaveRecordsDao;
import com.friday.po.LeaveRecords;
import com.friday.util.DBUtil;

public class LeaveRecordsDaoImpl implements LeaveRecordsDao {
	
	private Connection conn = null;
	private Statement stmt = null;
	private PreparedStatement pStmt = null;
	private ResultSet rs = null;
	private DBUtil util = null;
	
	@Override
	public int insertLeaveRecord(LeaveRecords le) throws Exception {
		String sql = "insert into leaverecords values(?,?,?,?)";
		int num = 0;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, le.getId());
			pStmt.setString(2, le.getEmployee());
			pStmt.setString(3, le.getDepartment());
			pStmt.setDate(4, le.getDate());
			
			num = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
//			e.printStackTrace();
			throw new Exception("leaverecorddao insert error!");
		}finally{
			util.close(pStmt);
			util.close(conn);
		}
		
		return num;
	}

	@Override
	public int updateDifferenceById(LeaveRecords le) throws Exception {
		String sql = "update leaverecords set employee = ? , department = ? , date = ? where id = ?";
		int num = 0;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			
			pStmt.setString(4, le.getId());
			pStmt.setString(1, le.getEmployee());
			pStmt.setString(2, le.getDepartment());
			pStmt.setDate(3, le.getDate());
			
			num = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
//			e.printStackTrace();
			throw new Exception("leaverecordsdao update error!");
		}finally{
			util.close(pStmt);
			util.close(conn);
		}
		
		return num;
	}

	@Override
	public int deleteById(String id) throws Exception {
		String sql = "delete from leaverecords where id = ?";
		int num = 0;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			
			pStmt.setString(1, id);
			
			num = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
//			e.printStackTrace();
			throw new Exception("leaverecordsdao delete error!");
		}finally{
			util.close(pStmt);
			util.close(conn);
		}
		
		return num;
	}

	@Override
	public LeaveRecords queryById(String id) throws Exception {
		String sql = "select * from leaverecords where id = ?";
		LeaveRecords le = new LeaveRecords();
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, id);
			rs = pStmt.executeQuery();
			
			while(rs.next())
			{
				le.setDate(rs.getDate("date"));
				le.setDepartment(rs.getString("department"));
				le.setEmployee("employee");
				le.setId(rs.getString("id"));
			}
		} catch (Exception e) {
			throw new Exception("leaverecordsdao querybyid error!");
		}finally{
			util.close(pStmt);
			util.close(conn);
		}
		
		return le;
	}

	@Override
	public List<LeaveRecords> queryAll() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	
	private List<LeaveRecords> queryBySql(String sql) throws Exception {
		List<LeaveRecords> list = new ArrayList<LeaveRecords>();
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			stmt = conn.createStatement();
			
			rs = stmt.executeQuery(sql);

			while(rs.next())
			{
				LeaveRecords lea = new LeaveRecords();
				lea.setDate(rs.getDate("date"));
				lea.setDepartment(rs.getString("department"));
				lea.setEmployee("employee");
				lea.setId(rs.getString("id"));
				
				list.add(lea);
			}
			
		} catch (Exception e) {
			throw e;
		}finally{
			util.close(rs);
			util.close(stmt);
			util.close(conn);
		}
		
		return list;
	}

	@Override
	public List<LeaveRecords> queryByEmployee(String employee) throws Exception {
		String sql = "select * from leaverecords where employee = '" + employee + "'";
		List<LeaveRecords> list = new ArrayList<LeaveRecords>();
		list = this.queryBySql(sql);
		return list;
	}

	@Override
	public List<LeaveRecords> queryByDepartment(String department)
			throws Exception {
		String sql = "select * from leaverecords where department = '" + department + "'";
		List<LeaveRecords> list = new ArrayList<LeaveRecords>();
		list = this.queryBySql(sql);
		return list;
	}

	@Override
	public List<LeaveRecords> queryByDate(Date date) throws Exception {
		String sql = "select * from leaverecords where date = " + date + "";
		List<LeaveRecords> list = new ArrayList<LeaveRecords>();
		list = this.queryBySql(sql);
		return list;
	}

	@Override
	public List<LeaveRecords> queryByEmployeeAndDate(String employee, Date date)
			throws Exception {
		String sql = "select * from leaverecords where employee = '" + employee + "'" + " and date = " + date + "";
		List<LeaveRecords> list = new ArrayList<LeaveRecords>();
		list = this.queryBySql(sql);
		return list;
	}

	@Override
	public List<LeaveRecords> queryByDepartmentAndDate(String department,
			Date date) throws Exception {
		String sql = "select * from leaverecords where department = '" + department + "'" + " and date = " + date + "";
		List<LeaveRecords> list = new ArrayList<LeaveRecords>();
		list = this.queryBySql(sql);
		return list;
	}

	@Override
	public List<LeaveRecords> queryByDepartmentBetweenDate(String department,
			Date startdate, Date enddate) throws Exception {
		String sql = "select * from leaverecords where department = '" + department + "' and date between '"+ startdate + "' and '"+ enddate + "'";
		List<LeaveRecords> list = new ArrayList<LeaveRecords>();
		list = this.queryBySql(sql);
		return list;
	}


}
